﻿## Get-InactiveActiveSyncUsers.ps1
##
## List all users activated for Activesync and whether or not there is any recent activity
## Activity is determined by plumbing the IIS logs since OWA 2003 is still in use.  (No Get-ActiveSyncDeviceStatistics)
##
## Author: David Ray
## Latest Modification: 1/17/2011 - fix mailing problem
##

param ([switch]$SendEmail = $false, [string]$OneOffEmailAddr = "default")
# By default do not send email.

## Setup Variables ######
$defaultTo = @("CHWIT-EnterpriseSecurityAdministration@chw.edu")
$defaultCc = @("DL-GIS-MessagingHealthcare1@ps.net","DL-GIS-MessagingWireless@ps.net","Bill_Branson@dell.com")
$defaultBcc = @("ExchangeOp@ps.net")
$defaultFrom = "ExchangeOp@ps.net"
$subject = "CHW Monthly ActiveSync Usage Report"
$emailMessage = "The attached file reports all enabled ActiveSync devices and indicates whether there has been recent usage along with other information about the account."
$SMTPserver = "smtp-relay.chw.edu"
$reportName = "CHWActiveSyncUsageReport" + $(Get-Date -Format "yyMMdd")
$reportPath = "E:\Reports\ActiveSyncReport"
## Setup Variables ######


## Import the most recent ActiveSync Usage report.  Select a subset of the fields and rename a couple of them.  
## 
Write-Host "Retrieving ActiveSync Usage Data from recent report"
$AsyncActive = Import-Csv \\chw-cas-001\admin$\scripts\CHWActiveSyncReport.csv `
	| Select-Object @{Name="SamAccountName";Expression={$_.UserID}},DisplayName,Alias,Phone,@{Name="PrimarySMTPAddress";Expression={$_.Email}}  `
	| Sort-Object SamAccountName	

## Get list of all users enabled for ActiveSync selecting a subset of fields
Write-Host "Getting all users with ActiveSync enabled"
$AsyncEnabled = Get-CASMailbox -ResultSize Unlimited -Filter {ActiveSyncEnabled -eq $true} `
	| Get-Mailbox | Select-Object SamAccountName,DisplayName,Alias,@{Name="PrimarySMTPAddress";Expression={$_.PrimarySMTPAddress.ToString()}},Servername,ExchangeUserAccountControl,RecipientTypeDetails `
	| Sort-Object SamAccountName

Write-Host "Correlate lists and build report data array"
## inactiveUsers are the result of the Compare-Object command that have "<=" in the SideIndicator field
$InactiveUsers = Compare-Object $AsyncEnabled $AsyncActive -Property Alias | Where-Object {$_.SideIndicator -eq "<="} 

## build an array of the alias's from inactiveUsers
$InactAlias = @()
$InactiveUsers | ForEach-Object {$InactAlias += $_.alias}
$ActAlias = @()
$AsyncActive | ForEach-Object {$ActAlias += $_.alias}

##empty array for the final result
$AllAsync = @()
#$AsyncUser = New-Object PSObject  ##the objects that will go into the final result array
Foreach ($AsyncUser1 in $AsyncEnabled) {   ## loop through everyone in the AsyncEnabled collection
	if (("david.ray.002","async2003","async2007") -contains $AsyncUser1.alias -or $AsyncUser1.alias -like "CAS_*") {continue} #skip test users
	elseif ($ActAlias -contains $AsyncUser1.alias) {
		## for each active enabled user loop through the activity collection for the user (gets users with multiple devices)
		Foreach ($AsyncUser in $AsyncActive | Where-Object {$_.alias -eq $AsyncUser1.alias}) {
			$AUser = New-Object PSObject
			$AUser | Add-Member -MemberType NoteProperty -Name Displayname -Value $AsyncUser.Displayname
			$AUser | Add-Member -MemberType NoteProperty -Name UserID -Value $AsyncUser.SamAccountName
			$AUser | Add-Member -MemberType NoteProperty -Name Alias -Value $AsyncUser.Alias
			$AUActive = if ($inactAlias -contains $AsyncUser.Alias) {"No"} else {"Yes"}
			$AUser | Add-Member -MemberType NoteProperty -Name "On Recent 30-Day Activity Report" -Value $AUActive
			#$AUser | Add-Member -MemberType NoteProperty -Name Phone -Value $AsyncActiveHash.($AsyncUser.Alias)
			$AUser | Add-Member -MemberType NoteProperty -Name Phone -Value $AsyncUser.Phone
			$AUser | Add-Member -MemberType NoteProperty -Name "Exchange Server" -Value $AsyncUser1.Servername
			$AUserMailboxType = if ($AsyncUser1.RecipientTypeDetails -eq "LegacyMailbox") {"2003"} else {"2007"}
			$AUser | Add-Member -MemberType NoteProperty -Name "Exchange Version" -Value $AUserMailboxType
			$AUser | Add-Member -MemberType NoteProperty -Name Email -Value $AsyncUser.PrimarySMTPAddress
			$AUStatus = if ($AsyncUser.exchangeuseraccountcontrol -eq "AccountDisabled") {"Disabled"} else {"Enabled"}
			$AUser | Add-Member -MemberType NoteProperty -Name "Account Status" -Value $AUStatus
			if ($AUserMailboxType -eq "2007") {$ASyncPolicy = (Get-CASMailbox $AsyncUser1.Alias).ActiveSyncMailboxPolicy} else {$aSyncPolicy = "<on 2003>"}
			$AUser | Add-Member -MemberType NoteProperty -Name "ActiveSync Mailbox Policy" -Value $ASyncPolicy
			$AUser | Add-Member -MemberType NoteProperty -Name City	-Value (Get-User $AsyncUser1.Alias).City
			$AUser | Add-Member -MemberType NoteProperty -Name Office -Value (Get-User $AsyncUser1.Alias).Office
			if ( (Get-ActiveSyncDeviceStatistics -Mailbox $AsyncUser1.alias) -eq $null) {$ASyncServer = "OWA 2003"} else {$ASyncServer = "CAS 2007"}
			$AUser | Add-Member -MemberType NoteProperty -Name "Async Server" -Value $ASyncServer
			$AllAsync += $AUser
		}
	}
	else {
	## for each inactive enabled user
		$AUser = New-Object PSObject
		$AUser | Add-Member -MemberType NoteProperty -Name Displayname -Value $AsyncUser1.Displayname
		$AUser | Add-Member -MemberType NoteProperty -Name UserID -Value $AsyncUser1.SamAccountName
		$AUser | Add-Member -MemberType NoteProperty -Name Alias -Value $AsyncUser1.Alias
		$AUActive = if ($inactAlias -contains $AsyncUser1.Alias) {"No"} else {"Yes"}
		$AUser | Add-Member -MemberType NoteProperty -Name "On Recent 30-Day Activity Report" -Value $AUActive
		$AUser | Add-Member -MemberType NoteProperty -Name Phone -Value $AsyncUser1.Phone
		$AUser | Add-Member -MemberType NoteProperty -Name "Exchange Server" -Value $AsyncUser1.Servername
		$AUserMailboxType = if ($AsyncUser1.RecipientTypeDetails -eq "LegacyMailbox") {"2003"} else {"2007"}
		$AUser | Add-Member -MemberType NoteProperty -Name "Exchange Version" -Value $AUserMailboxType
		$AUser | Add-Member -MemberType NoteProperty -Name Email -Value $AsyncUser1.PrimarySMTPAddress
		$AUStatus = if ($AsyncUser1.exchangeuseraccountcontrol -eq "AccountDisabled") {"Disabled"} else {"Enabled"}
		$AUser | Add-Member -MemberType NoteProperty -Name "Account Status" -Value $AUStatus
		
		
		if ($AUserMailboxType -eq "2007") {$ASyncPolicy = (Get-CASMailbox $AsyncUser1.Alias).ActiveSyncMailboxPolicy} else {$ASyncPolicy = "<on 2003>"}
		$AUser | Add-Member -MemberType NoteProperty -Name "ActiveSync Mailbox Policy" -Value $ASyncPolicy
		$AUser | Add-Member -MemberType NoteProperty -Name City	-Value (Get-User $AsyncUser1.Alias).City
		$AUser | Add-Member -MemberType NoteProperty -Name Office -Value (Get-User $AsyncUser1.Alias).Office
		$AUser | Add-Member -MemberType NoteProperty -Name "Async Server" -Value ""
		$AllAsync += $AUser
	}
}
$AllAsync | Export-Csv -NoTypeInformation "$($reportPath)\$($reportName).csv"



##
## Save all this out to Excel too and try to prettify it some.
##

## The constants that we will use

$xlCenter = -4108
$xlCellValue = 1
$xlEqual = 3
$xl3TrafficLights2 = 5
$xl3Symbols = 7
$xlConditionValueNumber = 0
$xlIconSet = 6
$xlAutomatic = -4105
$xlBottom = -4107
$xlContinuous = 1
$xlThin = 2
$xlBetween = 1
$xlNotBetween = 2
$xlEqual = 3
$xlNotEqual = 4
$xlGreater = 5
$xlLess = 6
$xlGreaterEqual = 7
$xlLessEqual = 8
$xlOpenXMLWorkbook = 51
$xlToRight = -4161


# Create an instance of Excel
$Excel = New-Object -comobject Excel.Application
$Excel.visible = $True 
$bucket = $Excel.Workbooks.Open("$($reportPath)\$($reportName).csv")  #$bucket is simply a variable to catch unwanted output so it doesn't go to screen (bit-bucket)
$Worksheet = $Excel.Worksheets.Item(1)
$Worksheet.Name = "CHWActiveSyncUsageReport"

  
# Make Columns wide enough for content
$Worksheet.Range("G:G").entireColumn.ColumnWidth = 10
$Worksheet.Range("I:I").entireColumn.ColumnWidth = 8

# Add a little formatting to the column header
$Range = $Worksheet.Range("A1",$Worksheet.Range("A1").End($xlToRight))
$Style = $Info.UsedRange
$Range.Interior.ColorIndex = 19
$Range.Font.ColorIndex = 11
$Range.Font.Bold = $True
$Range.Borders.LineStyle = $xlContinuous
$Range.Borders.ColorIndex = 0
$Range.Borders.TintAndShade = 0
$Range.Borders.Weight = $xlThin
$Range.WrapText = $true

$bucket= $Worksheet.Range("D:D").FormatConditions.Add($xlCellValue, $xlEqual, '="No"')
$Worksheet.Range("D:D").FormatConditions.Item(1).Interior.Color = 13551615
$Worksheet.Range("D:D").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
$Worksheet.Range("D:D").FormatConditions.Item(1).Interior.TintAndShade = 0
$Worksheet.Range("D:D").FormatConditions.Item(1).Font.Color = -16383844
$Worksheet.Range("D:D").FormatConditions.Item(1).Font.TintAndShade = 0


$bucket = $Worksheet.Range("D:D").FormatConditions.Add($xlCellValue, $xlEqual, '="Yes"')
$Worksheet.Range("D:D").FormatConditions.Item(2).Interior.Color = 13561798
$Worksheet.Range("D:D").FormatConditions.Item(2).Interior.PatternColorIndex = $xlAutomatic
$Worksheet.Range("D:D").FormatConditions.Item(2).Interior.TintAndShade = 0
$Worksheet.Range("D:D").FormatConditions.Item(2).Font.Color = -16752384
$Worksheet.Range("D:D").FormatConditions.Item(2).Font.TintAndShade = 0


 

# Make Columns wide enough for content
$bucket = $Worksheet.Range("A:M").entireColumn.Autofit()


if (Test-Path "$($ReportPath)\$($ReportName).xlsx") {Remove-Item "$($ReportPath)\$($ReportName).xlsx"}
$Excel.Workbooks.Item(1).SaveAs("$($ReportPath)\$($ReportName).xlsx",$xlOpenXMLWorkbook)
#Sleep 5 ## Time to admire the file
$Excel.Quit()
$Worksheet = $null
$Excel = $null
[GC]::Collect()


## Send Mail
	if ($sendEmail.IsPresent) {
		Sleep 10  ##Slow down a bit so Excel can release the file
		if ($OneOffEmailAddr -eq "default") {
			$to = $defaultTo
			$cc = $defaultCc
			$bcc = $defaultBcc
			}
		else {
			$to = $OneOffEmailAddr
			$cc = ""
			$bcc = ""
		}
		
		$emailbody = @"
<html>
<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=Generator content="Microsoft Word 14 (filtered)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin-top:0in;
	margin-right:0in;
	margin-bottom:10.0pt;
	margin-left:0in;
	line-height:115%;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
	{mso-style-link:"Balloon Text Char";
	margin:0in;
	margin-bottom:.0001pt;
	font-size:8.0pt;
	font-family:"Tahoma","sans-serif";}
span.MsoPlaceholderText
	{color:gray;}
span.BalloonTextChar
	{mso-style-name:"Balloon Text Char";
	mso-style-link:"Balloon Text";
	font-family:"Tahoma","sans-serif";}
.MsoChpDefault
	{font-family:"Calibri","sans-serif";}
.MsoPapDefault
	{margin-bottom:10.0pt;
	line-height:115%;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=WordSection1>
<p class=MsoNormal>CHW Messaging Reporting</p>
<p class=MsoNormal>$($emailMessage)</p>
<div class=MsoNormal align=center style='text-align:center'>
<hr size=2 width="100%" align=center>
</div>
<p class=MsoNormal><span style='font-size:9.0pt;line-height:115%;color:#BFBFBF'>Scripted
in Powershell by <a href="mailto:david_f_ray@dell.com"><span style='color:#0000BF'>David
Ray</span></a> {$($MyInvocation.MyCommand.Name)}</span></p>
<p class=MsoNormal>&nbsp;</p>
</div>
</body>
</html>
"@

		$mailer = new-object Net.Mail.SMTPclient($SMTPserver)
		$msg = new-object Net.Mail.MailMessage
		$msg.From = new-object System.Net.Mail.MailAddress($defaultFrom)
		$msg.Subject = $subject
		$msg.Body = $emailbody
		$to | foreach { $msg.To.Add($_)}
		if ($cc.Length -gt 0) {$cc | ForEach-Object {$msg.cc.Add($_)}}
		if ($bcc.Length -gt 0) {$bcc | ForEach-Object {$msg.bcc.Add($_)}}
		$attachment = new-object Net.Mail.Attachment("$($reportPath)\$($reportName).xlsx")
		$msg.attachments.add($attachment)
		$msg.IsBodyHtml = $true

		$mailer.send($msg)
		$attachment = $null
		$msg = $null
		$mailer = $null
	}

[GC]::Collect()  ## Clean up the mess